package model.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import controller.util.ControllerHelper;
import model.pojo.LapTop;
import model.pojo.SanPham;
import model.util.DataHelper;

public class SanPhamDAO 
{
	public static ArrayList<ArrayList<String>> LaySanPhamMoi(int  option){
//	{
		DataHelper db = new DataHelper();
        if(!db.open())
            return null ;
        String sp = String.format(ControllerHelper.getStoreProcedureString("LaySanPhamMoi"),option);
        ArrayList<ArrayList<String>> arr = null ;
        ResultSet rs = db.executeQuery(sp);
        
        try
        {
        	if(rs.next())
        	{
	            arr = new ArrayList<ArrayList<String>>();
	            
	            do
	            {
	            	ArrayList<String> temp = new ArrayList<String>();
	            	temp.add(rs.getString("MaSP"));//ma sp
	            	temp.add(rs.getString("TenSP"));//Ten sp
	            	temp.add(rs.getString("GiaBan"));//Gia ban
	            	temp.add(rs.getString("Link"));//link hinh
	            	temp.add(rs.getString("TenLoaiSP"));//ten loai sp
	            	
	            	arr.add(temp);
	            }while(rs.next());
        	}
        }
        catch(Exception ex){}
        db.close();
        return arr;
	}
	
	public static ArrayList<ArrayList<String>> LaySanPhamNoiBat(int  option){
//	{		
		DataHelper db = new DataHelper();
        if(!db.open())
            return null ;
        String sp = String.format(ControllerHelper.getStoreProcedureString("LaySanPhamNB"),option);
        ArrayList<ArrayList<String>> arr = null ;
        ResultSet rs = db.executeQuery(sp);
        
        try
        {
        	if(rs.next())
        	{
	            arr = new ArrayList<ArrayList<String>>();
	            
	            do
	            {
	            	ArrayList<String> temp = new ArrayList<String>();
	            	temp.add(rs.getString("MaSP"));//ma sp
	            	temp.add(rs.getString("TenSP"));//Ten sp
	            	temp.add(rs.getString("GiaBan"));//Gia ban
	            	temp.add(rs.getString("Link"));//link hinh
	            	temp.add(rs.getString("TenLoaiSP"));//ten loai sp
	            	
	            	arr.add(temp);
	            }while(rs.next());
        	}
        }
        catch(Exception ex){}
        db.close();
        return arr;
	}
	
	public static ArrayList<ArrayList<String>> Top10SanPhamBanNhieu(int  option){
//	{		
		DataHelper db = new DataHelper();
        if(!db.open())
            return null ;
        String sp = String.format(ControllerHelper.getStoreProcedureString("Top10SanPhamBanNhieu"),option);
        ArrayList<ArrayList<String>> arr = null ;
        ResultSet rs = db.executeQuery(sp);
       
        try
        {
        	if(rs.next())
        	{
	            arr = new ArrayList<ArrayList<String>>();
	            
	            do
	            {
	            	ArrayList<String> temp = new ArrayList<String>();
	            	temp.add(rs.getString("MaSP"));//ma sp
	            	temp.add(rs.getString("TenSP"));//Ten sp
	            	temp.add(rs.getString("GiaBan"));//Gia ban
	            	temp.add(rs.getString("Link"));//link hinh
	            	temp.add(rs.getString("TenLoaiSP"));//ten loai sp
	            	
	            	arr.add(temp);
	            }while(rs.next());
        	}
        }
        catch(Exception ex){}
        db.close();
        return arr;
	}
	
	public static ArrayList<String> ChiTietLapTop(String MaSP){
//	{		
		DataHelper db = new DataHelper();
        if(!db.open())
            return null ;
        String sp = String.format(ControllerHelper.getStoreProcedureString("ChiTietLapTop"),MaSP);
        ArrayList<String> arr = null ;
        ResultSet rs = db.executeQuery(sp);
        
        try
        {
        	if(rs.next())
        	{
	            arr = new ArrayList<String>();
	            
	            do
	            {
	                arr.add(rs.getString("MaSP"));//MaSP
	                arr.add(rs.getString("TenSP"));//TenSP
	                arr.add(rs.getString("GiaBan"));//GiaBan
	                arr.add(rs.getString("HienTrang"));//HienTrang
	                arr.add(rs.getString("ThoiGianBaoHanh"));//ThoiGianBH
	                arr.add(rs.getString("ThongTinKM"));//ThongTinKM
	                arr.add(rs.getString("MicroProcessor"));//MicroProcessor
	                arr.add(rs.getString("Cache"));//Cache
	                arr.add(rs.getString("Memory"));//Ram
	                arr.add(rs.getString("VideoGraphic"));//VideoGraphic
	                arr.add(rs.getString("HDD"));//HDD
	                arr.add(rs.getString("MultimediaDrive"));//MultimediaDrive
	                arr.add(rs.getString("Integrated"));//Integrated
	                arr.add(rs.getString("Display"));//Display
	                arr.add(rs.getString("NetWorkCard"));//NetWorkCard
	                arr.add(rs.getString("WirelessConnectivity"));//Wireless 	
	                arr.add(rs.getString("Keyboard"));//Keyboard
	                arr.add(rs.getString("PointingDevice"));//Pointing
	                arr.add(rs.getString("ExternalPorts"));//ExternalPort
	                arr.add(rs.getString("Weight"));//weight
	                arr.add(rs.getString("Soft"));//Soft
	                arr.add(rs.getString("Link"));//linkHinhAnh
	                
	                if(rs.getInt("SoLuongTon") >= 1)
	                	arr.add("Còn Hàng");//SoLuongTon
	                else
	                	arr.add("Hết Hàng");//SoLuongTon
	                
	                arr.add(rs.getString("DongSanPham"));//Dòng Sản Phẩm
	            }while(rs.next());
        	}
        }
        catch(Exception ex){}
        db.close();
        return arr;
	}
	
	public static ArrayList<String> ChiTietSanPham(String MaSP){
//		{		
			DataHelper db = new DataHelper();
	        if(!db.open())
	            return null ;
	        String sp = String.format(ControllerHelper.getStoreProcedureString("ChiTietSanPham"),MaSP);
	        ArrayList<String> arr = null ;
	        ResultSet rs = db.executeQuery(sp);
	        
	        try
	        {
	        	if(rs.next())
	        	{
		            arr = new ArrayList<String>();
		            
		            do
		            {
		            	arr.add(rs.getString("MaSP"));//MaSP
		                arr.add(rs.getString("TenSP"));//TenSP
		                arr.add(rs.getString("GiaBan"));//GiaBan
		                arr.add(rs.getString("HienTrang"));//HienTrang
		                arr.add(rs.getString("ThoiGianBaoHanh"));//ThoiGianBH
		                arr.add(rs.getString("ThongTinKM"));//ThongTinKM
		                
		            	if(rs.getString(1).equals("3"))
		            	{
			                arr.add(rs.getString("Model"));//Model
			                arr.add(rs.getString("LoaiManHinh"));//LoaiManHinh
			                arr.add(rs.getString("KichThuocDiemAnh"));//KichThuocDiemAnh
			                arr.add(rs.getString("CuongDoSang"));//CuongDoSang
			                arr.add(rs.getString("DoTuongPhan"));//DoTuongPhan
			                arr.add(rs.getString("GocNhin"));//GocNhin
			                arr.add(rs.getString("DoPhanGiai"));//DoPhanGiai
			                arr.add(rs.getString("KhaNangHienThiMau"));//KhaNangHienThiMau
			                arr.add(rs.getString("ThoiGianDapUng"));//ThoiGianDapUng
			                arr.add(rs.getString("KetNoiHoTro"));//KetNoiHoTro	
			                arr.add(rs.getString("HieuDienTheVao"));//HieuDienTheVao
			                arr.add(rs.getString("CongSuatTieuThu"));//CongSuatTieuThu
			                arr.add(rs.getString("TrongLuong"));//TrongLuong
			                arr.add(rs.getString("KichThuoc"));//KichThuoc
			                arr.add(rs.getString("Link"));//LinkHinhAnh
			                
			                if(rs.getInt("SoLuongTon") >= 1)
			                	arr.add("Còn Hàng");//SoLuongTon
			                else
			                	arr.add("Hết Hàng");//SoLuongTon
			                
			                arr.add(rs.getString("DongSanPham"));//Dòng Sản Phẩm
		            	}
		            	else
		            	{
		            		arr.add(rs.getString("Link"));//Link hinh
		            		
		            		if(rs.getInt("SoLuongTon") >= 1)
			                	arr.add("Còn Hàng");//SoLuongTon
			                else
			                	arr.add("Hết Hàng");//SoLuongTon
			                
		            		arr.add(rs.getString("DongSanPham"));//Dong san pham
		            		arr.add(rs.getString("MoTaSP"));//Mota san pham
		            	}
		            }while(rs.next());
	        	}
	        }
	        catch(Exception ex){}
	        db.close();
	        return arr;
		}
	
	public static ArrayList<ArrayList<String>> SanPhamCungDongSanPham(String DongSanPham){
//	{		
		DataHelper db = new DataHelper();
        if(!db.open())
            return null ;
        String sp = String.format(ControllerHelper.getStoreProcedureString("SanPhamCungDongSanPham"),DongSanPham);
        ArrayList<ArrayList<String>> arr = null ;
        ResultSet rs = db.executeQuery(sp);
        try
        {
        	if(rs.next())
        	{
                arr = new ArrayList<ArrayList<String>>();
                
                do
                {
                	ArrayList<String> temp = new ArrayList<String>();
                	temp.add(rs.getString("MaSP"));//MaSP
                	temp.add(rs.getString("TenSP"));//TenSP
                	temp.add(rs.getString("GiaBan"));//GiaBan
                	temp.add(rs.getString("MoTaSP"));//MoTaSP
                	temp.add(rs.getString("ThoiGianBaoHanh"));//ThoiGianBH

                    if(rs.getInt("SoLuongTon") >= 1)
                    	temp.add("Còn Hàng");//SoLuongTon
                    else
                    	temp.add("Hết Hàng");//SoLuongTon
                    
                    temp.add(rs.getString("SoLuotXem"));//SoLuotXem
                    temp.add(rs.getString("Link"));//HinhAnh
                    
                    arr.add(temp);
                }while(rs.next());
            }
        }
        catch(Exception ex){}
        db.close();
        return arr;
	}
	
	public static ArrayList<ArrayList<String>> TimKiem(String Key){
//	{		
		DataHelper db = new DataHelper();
        if(!db.open())
            return null ;
        String sp = String.format(ControllerHelper.getStoreProcedureString("TimKiemTheoMoTa"),Key);
        ArrayList<ArrayList<String>> arr = null ;
        ResultSet rs = db.executeQuery(sp);
		try 
		{
			if(rs.next())
			{
		        arr = new ArrayList<ArrayList<String>>();
		        do
		        {
		        	ArrayList<String> temp = new ArrayList<String>();
		        	
		        	temp.add(rs.getString("MaSP"));//MaSP
		        	temp.add(rs.getString("TenSP"));//TenSP
		        	temp.add(rs.getString("GiaBan"));//GiaBan
		        	temp.add(rs.getString("MoTaSP"));//MoTaSP
		        	temp.add(rs.getString("ThoiGianBaoHanh"));//ThoiGianBH

		            if(rs.getInt("SoLuongTon") >= 1)
		            	temp.add("Còn Hàng");//SoLuongTon
		            else
		            	temp.add("Hết Hàng");//SoLuongTon
		            
		            temp.add(rs.getString("SoLuotXem"));//SoLuotXem
		            temp.add(rs.getString("Link"));//HinhAnh
		            temp.add(rs.getString("urlChiTiet"));
		            arr.add(temp);
		        }while(rs.next());
			}
		} 
		catch (SQLException e) {}
		sp = String.format(ControllerHelper.getStoreProcedureString("TimKiemTheoTen"),Key);
        rs = db.executeQuery(sp);
        try 
		{
			if(rs.next())
			{
				if(arr == null)
					arr = new ArrayList<ArrayList<String>>();
		        do
		        {
		        	ArrayList<String> temp = new ArrayList<String>();
		        	
		        	temp.add(rs.getString("MaSP"));//MaSP 0
		        	temp.add(rs.getString("TenSP"));//TenSP 1
		        	temp.add(rs.getString("GiaBan"));//GiaBan 2
		        	temp.add(rs.getString("MoTaSP"));//MoTaSP 3 
		        	temp.add(rs.getString("ThoiGianBaoHanh"));//ThoiGianBH 4

		            if(rs.getInt("SoLuongTon") >= 1) // 5
		            	temp.add("Còn Hàng");//SoLuongTon
		            else
		            	temp.add("Hết Hàng");//SoLuongTon
		            
		            temp.add(rs.getString("SoLuotXem"));//SoLuotXem 6
		            temp.add(rs.getString("Link"));//HinhAnh 7
		            temp.add(rs.getString("urlChiTiet")); // 8
		            arr.add(temp);
		        }while(rs.next());
			}
		} 
		catch (SQLException e) {}
        db.close();
        return arr;
	}
	
	public static ArrayList<ArrayList<String>> BaoGia(){
//	{		
		DataHelper db = new DataHelper();
        if(!db.open())
            return null ;
        String sp = ControllerHelper.getStoreProcedureString("BaoGia");
        ArrayList<ArrayList<String>> arr = null ;
        ResultSet rs = db.executeQuery(sp);
        try
        {
        	if(rs.next())
        	{
                arr = new ArrayList<ArrayList<String>>();
                
                do
                {
                	ArrayList<String> temp = new ArrayList<String>();
                	temp.add(rs.getString("MaSP"));//MaSP
                	temp.add(rs.getString("TenSP"));//TenSP
                	temp.add(rs.getString("GiaBan"));//GiaBan
                	temp.add(rs.getString("TenLoaiSanPham"));//loai sp
                	temp.add(rs.getString("MoTaSP"));//MoTaSP
                    arr.add(temp);
                }while(rs.next());
            }
        }
        catch(Exception ex){}
        db.close();
        return arr;
	}
	
	public static ArrayList<ArrayList<String>> LaySanPham(int op , String val , String val1 , String val2){
//	{		
		DataHelper db = new DataHelper();
        if(!db.open())
            return null ;
        
        String sp = String.format(ControllerHelper.getStoreProcedureString("LaySPTheoGia"),val,val1,val2);
        if(op == 1)
        	sp = String.format(ControllerHelper.getStoreProcedureString("LayLaptopTheoCPU"),val);
        if(op == 2)
        	sp = String.format(ControllerHelper.getStoreProcedureString("LayLaptopTheoRam"),val);
        if(op == 3)
        	sp = String.format(ControllerHelper.getStoreProcedureString("LayLaptopTheoHDD"),val);
        if(op == 4)
        	sp = String.format(ControllerHelper.getStoreProcedureString("LayLaptopTheoCell"),val);
        
        ArrayList<ArrayList<String>> arr = null ;
        ResultSet rs = db.executeQuery(sp);
		try 
		{
			if(rs.next())
			{
		        arr = new ArrayList<ArrayList<String>>();
		        do
		        {
		        	ArrayList<String> temp = new ArrayList<String>();
		        	
		        	temp.add(rs.getString("MaSP"));//MaSP
		        	temp.add(rs.getString("TenSP"));//TenSP
		        	temp.add(rs.getString("GiaBan"));//GiaBan
		        	temp.add(rs.getString("MoTaSP"));//MoTaSP
		        	temp.add(rs.getString("ThoiGianBaoHanh"));//ThoiGianBH

		            if(rs.getInt("SoLuongTon") >= 1)
		            	temp.add("Còn Hàng");//SoLuongTon
		            else
		            	temp.add("Hết Hàng");//SoLuongTon
		            
		            temp.add(rs.getString("SoLuotXem"));//SoLuotXem
		            temp.add(rs.getString("Link"));//HinhAnh
		            temp.add(rs.getString("urlChiTiet"));
		            arr.add(temp);
		        }while(rs.next());
			}
		} 
		catch (SQLException e) {}
        db.close();
        return arr;
	}

	
	public static boolean ThemSanPham(SanPham sp)
    {
        boolean kq =false;
        DataHelper helper = new DataHelper();
        try
        {
            
            helper.open();
            ArrayList<Object> para = new ArrayList<Object>();
            para.add(sp.getTenSP());
            para.add(sp.getLoaiSP());
            para.add(sp.getMoTaSP());
            para.add(sp.getGiaBan());
            para.add(sp.getKhuyenMai());
            para.add(sp.getThoiGianBaoHanh());
            para.add(sp.getHangSanXuat());
            para.add(sp.getSoLuongTon());
            para.add(sp.getTinhTrang());
            para.add(sp.getHienTrang());
            para.add(sp.getMaDongSP());
            para.add(sp.getLinkHinhAnh());
            String stored = ControllerHelper.getStoreProcedureString("ThemSanPham");
            ResultSet result = helper.executeProcedure(stored, para);
            
            if(result.next())
            {
                int STT = result.getInt(1);
                sp.setMaSP(String.valueOf(STT));               
                kq=true;
            }
            //helper.close();
        }
        catch(Exception ex)
        {
        	//kq=false;
           //System.out.println(ex.getMessage());
        }
        finally
        {
            helper.close();
        }
        return kq;
    }	
	
	//kiem tra san pham theo ten KiemTraSP=
	public static boolean KiemTraSanPham(String ten)
	{
		boolean kq = false;
		DataHelper helper = new DataHelper();
        try
        {
            
            helper.open();
            ArrayList<Object> para = new ArrayList<Object>();	
            para.add(ten);
            String stored = ControllerHelper.getStoreProcedureString("KiemTraSP");
            ResultSet result = helper.executeProcedure(stored, para);
            
            if(result.next())
            {	                            
                kq=true;
            }
            //helper.close();
        }
        catch(Exception ex)
        {
        	//kq=false;
            //System.out.println(ex.getMessage());
        }
        finally
        {
            helper.close();
        }
		return kq;
	}
	//delete san pham theo ma
	public static boolean deleteSanPham(String ma)
	{
		boolean kq = false;
		DataHelper helper = new DataHelper();
        try
        {
            
            helper.open();
            ArrayList<Object> para = new ArrayList<Object>();	
            para.add(ma);
            String stored = ControllerHelper.getStoreProcedureString("XoaSp");
            //ResultSet result = helper.executeProcedure(stored, para);
            int kqDel = helper.executeInsProcedure(stored, para);
            if(kqDel !=-1)
            {	                            
                kq=true;
            }           
            //helper.close();
        }
        catch(Exception ex)
        {
        	//kq=false;
           //System.out.println(ex.getMessage());
        }
        finally
        {
            helper.close();
        }
		return kq;
	}
	//lay duong dan hinh anh
	public static String LayHinhAnh(String ma)
	{
		String kq = null;
		 DataHelper helper = new DataHelper();
        try
        {
            
            helper.open();
            ArrayList<Object> para = new ArrayList<Object>();
            para.add(ma);
            String stored = ControllerHelper.getStoreProcedureString("DuongDanHinh");
            ResultSet result = helper.executeProcedure(stored, para);
            
            if(result.next())
            {
                kq = result.getString("Link");                            
               
            }
            //helper.close();
        }
        catch(Exception ex)
        {
        	//kq=false;
            //System.out.println(ex.getMessage());
        }
        finally
        {
            helper.close();
        }
		return kq;
	}
}
